package com.yc.forum.util;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;

public class DBHelper {

    private static String url;
    private static String user;
    private static String pwd;
    private static Properties props = new Properties();
    private static ThreadLocal<Connection> localConnection = new ThreadLocal<>();

    static {
        try (InputStream in = DBHelper.class.getClassLoader().getResourceAsStream("conn.properties")) {
            if (in != null) {
                props.load(in);
                init(props.getProperty("default"));
            }
        } catch (IOException e) {
            throw new RuntimeException("配置文件【conn.properties】读入失败！", e);
        }
    }

    public static void init(String dbname) {
        init(props.getProperty(dbname + "-url"),
                props.getProperty(dbname + "-user"),
                props.getProperty(dbname + "-pwd"),
                props.getProperty(dbname + "-driver"));
    }

    public static void init(String url, String user, String pwd, String driver) {
        try {
            DBHelper.url = url;
            DBHelper.user = user;
            DBHelper.pwd = pwd;
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("数据库驱动【" + driver + "】加载失败", e);
        } catch (Exception e) {
            throw new RuntimeException("DBHelper初始化失败！", e);
        }
    }

    /**
     * 获取连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection conn = localConnection.get();
        if (conn != null)
            return conn;
        else
            try {
                return DriverManager.getConnection(url, user, pwd);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
    }

    /**
     * 带事务执行
     *
     * @param handler
     */
    public static <T> T execute(Handler<T> handler) {
        Connection conn = getConnection();
        try {
            localConnection.set(conn);
            conn.setAutoCommit(false);
            T ret = handler.execute(conn);
            conn.commit();
            return ret;
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (Exception e1) {
                throw new RuntimeException(e1);
            }
            throw new RuntimeException("SQL语句执行错误！", e);
        } finally {
            localConnection.remove();
            try {
                conn.close();
            } catch (Exception e1) {
                throw new RuntimeException(e1);
            }
        }
    }

    /**
     * 执行增删改SQL语句
     *
     * @param sql
     * @param params
     * @return
     */
    public static int update(String sql, Object... params) {
        return _execute(c -> {
            return prepareStatement(c, null, sql, params).executeUpdate();
        });
    }

    public static <T> T selectOneBean(String sql, Class<T> cls, Object... params) {
        return queryOne(sql, new BeanConverter<T>(cls), params);
    }

    public static Map<String, Object> selectOneMap(String sql, Object... params) {
        return queryOne(sql, MAP_CONVERTER, params);
    }

    public static <T> List<T> selectListBean(String sql, Class<T> cls, Object... params) {
        return queryList(sql, new BeanConverter<T>(cls), params);
    }

    public static List<Map<String, Object>> selectListMap(String sql, Object... params) {
        return queryList(sql, MAP_CONVERTER, params);
    }

    public static <T> T selectValue(String sql, Class<T> cls, Object... params) {
        return queryOne(sql, new ValueConverter<T>(cls), params);
    }

    public static long count(String sql, Object... params) {
        sql = "select count(*) from (" + sql + ") a";
        return selectValue(sql, long.class, params);
    }

    /**
     * 查询List结果
     */
    public static <T> List<T> queryList(String sql, Converter<T> converter, Object... params) {
        return _execute(c -> {
            PreparedStatement ps = prepareStatement(c, null, sql, params);
            ResultSet rs = ps.executeQuery();
            List<T> ret = new ArrayList<T>();
            for (; rs.next(); ) {
                T t = converter.convert(rs);
                ret.add(t);
            }
            return ret;
        });
    }

    /**
     * 查询单行结果
     */
    public static <T> T queryOne(String sql, Converter<T> converter, Object... params) {
        return _execute(c -> {
            PreparedStatement ps = prepareStatement(c, null, sql, params);
            // 执行语句
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                T ret = converter.convert(rs);
                if (rs.next()) {
                    throw new RuntimeException("结果集返回的记录行数大于1！");
                }
                return ret;
            }
            throw new RuntimeException("未查询到数据！");
        });
    }

    /**
     * 将数据库字段的名称转成 java 驼峰命名方式
     * 例如： user_name ==> userName
     */
    public static String toJavaName(String columnName) {
        // 先将字符串全部转成小写
        columnName = columnName.toLowerCase();
        // 判断是否包含下划线
        while (columnName.contains("_")) {
            // 查找下划线
            int i = columnName.indexOf("_");
            // 获取下划线后面的字符
            char c = columnName.charAt(i + 1);
            // 转成大写
            c = Character.toUpperCase(c);
            // 替换小写字符
            columnName = columnName.substring(0, i) + c + columnName.substring(i + 2);
        }
        return columnName;
    }

    /**
     * 将驼峰命名的名称转成数据库方式
     * 例如： userName ==> user_name
     */
    public static String toDBName(String fieldName) {
        return fieldName.replaceAll("([A-W])", "_$1");
    }

    /**
     * 转换器接口, 将结果集的当前行, 转成特定的对象
     */
    public static interface Converter<T> {
        T convert(ResultSet rs) throws SQLException;
    }

    /**
     * 结果集转 Map
     */
    public final static Converter<Map<String, Object>> MAP_CONVERTER = new Converter<Map<String, Object>>() {
        @Override
        public Map<String, Object> convert(ResultSet rs) throws SQLException {
            Map<String, Object> row = new LinkedHashMap<>();
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                row.put(rs.getMetaData().getColumnName(i + 1).toLowerCase(), rs.getObject(i + 1));
            }
            return row;
        }
    };

    /**
     * 结果集转 Bean
     */
    public final static class BeanConverter<T> implements Converter<T> {

        private Class<T> cls;

        public BeanConverter(Class<T> cls) {
            this.cls = cls;
        }

        @Override
        public T convert(ResultSet rs) throws SQLException {
            T ret;
            try {
                ret = cls.newInstance();
                for (Field f : cls.getDeclaredFields()) {
                    String cname = toDBName(f.getName());
                    String ctype = f.getType().getSimpleName();
                    String methodName = buildRSGetMethodName(ctype);
                    Method getMethod = rs.getClass().getMethod(methodName, String.class);
                    Object value = getMethod.invoke(rs, cname);
                    f.setAccessible(true);
                    f.set(ret, value);
                }
            } catch (InstantiationException | IllegalAccessException e) {
                throw new RuntimeException("创建实体对象失败!", e);
            } catch (NoSuchMethodException | SecurityException e) {
                throw new RuntimeException("获取 ResultSet 取值方法失败!", e);
            } catch (IllegalArgumentException | InvocationTargetException e) {
                throw new RuntimeException("执行 ResultSet 取值方法失败!", e);
            }
            return ret;
        }

    }

    /**
     * 结果集转 单值
     */
    public final static class ValueConverter<T> implements Converter<T> {

        private Class<T> cls;

        public ValueConverter(Class<T> cls) {
            this.cls = cls;
        }

        @SuppressWarnings("unchecked")
        @Override
        public T convert(ResultSet rs) throws SQLException {
            T ret;
            try {
                String ctype = cls.getSimpleName();
                String methodName = buildRSGetMethodName(ctype);
                Method getMethod = rs.getClass().getMethod(methodName, int.class);
                ret = (T) getMethod.invoke(rs, 1);
            } catch (NoSuchMethodException | SecurityException e) {
                throw new RuntimeException("获取 ResultSet 取值方法失败!", e);
            } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                throw new RuntimeException("执行 ResultSet 取值方法失败!", e);
            }
            return ret;
        }

    }

    private static String buildRSGetMethodName(String ctype) {
        if ("Integer".equals(ctype)) {
            ctype = "int";
        }
        ctype = ctype.substring(0, 1).toUpperCase() + ctype.substring(1);
        return "get" + ctype;
    }

    /**
     * 创建语句对象
     *
     * @param columns <ol><li>null 不返回自增列<li>length==0 返回主键自增值 (MySQL)<li>length > 0 返回指定列自增值 (Oracle)</ol>
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static PreparedStatement prepareStatement(String[] columns, String sql, Object... params)
            throws SQLException {
        return prepareStatement(getConnection(), columns, sql, params);
    }

    /**
     * 创建语句对象
     *
     * @param conn
     * @param columns <ol><li>null 不返回自增列<li>length==0 返回主键自增值 (MySQL)<li>length > 0 返回指定列自增值 (Oracle)</ol>
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static PreparedStatement prepareStatement(Connection conn, String[] columns, String sql, Object... params)
            throws SQLException {
        System.out.println("SQL：" + sql);
        System.out.println("参数：" + java.util.Arrays.toString(params));
        PreparedStatement ps;
        if (columns != null) {
            if (columns.length > 0) {
                ps = conn.prepareStatement(sql, columns);
            } else {
                ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            }
        } else {
            ps = conn.prepareStatement(sql);
        }
        // 设置参数
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
        return ps;
    }

    private static <T> T _execute(Handler<T> handler) {
        Connection conn = getConnection();
        try {
            return handler.execute(conn);
        } catch (Exception e) {
            throw new RuntimeException("SQL语句执行错误！", e);
        } finally {
            if (conn != localConnection.get()) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    public static interface Handler<T> {
        T execute(Connection conn) throws SQLException;
    }

    public static void main(String[] args) {
        System.out.println(DBHelper.count("select * from yh_user"));
    }

}
